﻿# -*- coding:utf-8 -*-

from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.table2JSON import Table2Json


class ItemDao(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections()

    def add_item(self, item_info):
        industry_name = item_info['industry_name']
        depart_name = item_info['depart_name']
        handler_name = item_info['handler_name']
        handler_phone = item_info['handler_phone']
        item_status = item_info['item_status']
        item_name = item_info['item_name']
        item_money = item_info['item_money']
        item_level = item_info['item_level']
        item_content = item_info['item_content']
        item_province = item_info['item_province']
        item_town = item_info['item_town']
        item_country = item_info['item_country']
        item_url = item_info['item_url']
        item_deadline = item_info['item_deadline']
        item_everyPrice = item_info['item_everyPrice']
        classfy_pid = item_info['classfy_pid']
        classfy_subname = item_info['classfy_subname']

        sql = "insert into zzh_item (industry_name,depart_name,handler_name,handler_phone,item_status,item_name," \
              "item_money,item_level,item_content,item_province,item_town,item_country,item_url,item_deadline,item_everyprice," \
              "classfy_pid,classfy_subname) " \
              "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cur = self.conn.cursor()
        result = cur.execute(sql, [industry_name, depart_name, handler_name, handler_phone, item_status, item_name,
                                   item_money, item_level, item_content, item_province, item_town, item_country,
                                   item_url,
                                   item_deadline, item_everyPrice, classfy_pid, classfy_subname])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return result

    def update_item(self, item_id, item_info):
        industry_name = item_info['industry_name']
        depart_name = item_info['depart_name']
        handler_name = item_info['handler_name']
        handler_phone = item_info['handler_phone']
        item_status = item_info['item_status']
        item_name = item_info['item_name']
        item_money = item_info['item_money']
        item_level = item_info['item_level']
        item_content = item_info['item_content']
        item_province = item_info['item_province']
        item_town = item_info['item_town']
        item_country = item_info['item_country']
        item_url = item_info['item_url']
        item_deadline = item_info['item_deadline']
        item_everyPrice = item_info['item_everyPrice']
        classfy_pid = item_info['classfy_pid']
        classfy_subname = item_info['classfy_subname']

        sql = "update zzh_item set industry_name=%s,depart_name=%s,handler_name=%s,handler_phone=%s,item_status=%s,item_name=%s," \
              "item_money=%s,item_level=%s,item_content=%s,item_province=%s,item_town=%s,item_country=%s,item_url=%s,item_deadline=%s," \
              "item_everyprice=%s,classfy_pid=%s,classfy_subname=%s " \
              "WHERE item_id=%s"
        cur = self.conn.cursor()
        result = cur.execute(sql, [industry_name, depart_name, handler_name, handler_phone, item_status, item_name,
                                   item_money, item_level, item_content, item_province, item_town, item_country,
                                   item_url,
                                   item_deadline, item_everyPrice, classfy_pid, classfy_subname, item_id])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return result

    def query_item(self):
        sql = "select * from zzh_item"
        cur = self.conn.cursor()
        rs = cur.execute(sql)
        item_list = cur.fetchall()
        item_json = Table2Json.item_list_to_json(item_list[:10])
        cur.close()
        self.conn.close()
        return item_json

    def get_items_by_classfy(self, classfyCode):
        cur = self.conn.cursor()
        sql = "select * from zzh_item WHERE classfy_pid=%s"
        rs = cur.execute(sql, [classfyCode])
        if rs:
            item_list = cur.fetchall()
            item_json = Table2Json.item_list_to_json(item_list)
        else:
            item_json = None

        cur.close()
        self.conn.close()
        return item_json

    '''根据条件查询item(已经弃用，改用下面的conditions（）方法)'''

    def query_item_ifcondition(self, **kw):
        item_area = kw['item_area']
        item_level = kw['item_level']
        item_status = kw['item_status']

        cur = self.conn.cursor()

        if item_area and item_level and item_status:
            arr = item_area.split('-')
            item_province = arr[0]
            item_town = arr[1]
            item_country = arr[2]

            sql = "select * from zzh_item WHERE item_province=%s AND item_town=%s AND item_country=%s " \
                  "AND item_level=%s AND item_status=%s"

            rs = cur.execute(sql, [item_province, item_town, item_country, item_level, item_status])
            if rs:
                items = cur.fetchall()
                itemsJson = Table2Json.item_list_to_json(items)
                cur.close()
                self.conn.close()
                return itemsJson
            else:
                return None
        elif item_area and item_level:
            arr = item_area.split('-')
            item_province = arr[0]
            item_town = arr[1]
            item_country = arr[2]

            sql = "select * from zzh_item WHERE item_province=%s AND item_town=%s AND item_country=%s " \
                  "AND item_level=%s"

            rs = cur.execute(sql, [item_province, item_town, item_country, item_level])
            if rs:
                items = cur.fetchall()
                itemsJson = Table2Json.item_list_to_json(items)
                cur.close()
                self.conn.close()
                return itemsJson
            else:
                return None
        elif item_area and item_status:
            arr = item_area.split('-')
            item_province = arr[0]
            item_town = arr[1]
            item_country = arr[2]

            sql = "select * from zzh_item WHERE item_province=%s AND item_town=%s AND item_country=%s " \
                  "AND item_status=%s"

            rs = cur.execute(sql, [item_province, item_town, item_country, item_status])
            if rs:
                items = cur.fetchall()
                itemsJson = Table2Json.item_list_to_json(items)
                cur.close()
                self.conn.close()
                return itemsJson
            else:
                return None
        elif item_status and item_level:

            sql = "select * from zzh_item WHERE " \
                  " item_level=%s AND item_status=%s"

            rs = cur.execute(sql, [item_level, item_status])
            if rs:
                items = cur.fetchall()
                itemsJson = Table2Json.item_list_to_json(items)
                cur.close()
                self.conn.close()
                return itemsJson
            else:
                return None
        elif item_area:
            arr = item_area.split('-')
            item_province = arr[0]
            item_town = arr[1]
            item_country = arr[2]

            sql = "select * from zzh_item WHERE item_province=%s AND item_town=%s AND item_country=%s "

            rs = cur.execute(sql, [item_province, item_town, item_country])
            if rs:
                items = cur.fetchall()
                itemsJson = Table2Json.item_list_to_json(items)
                cur.close()
                self.conn.close()
                return itemsJson
            else:
                return None
        elif item_level:
            sql = "select * from zzh_item WHERE item_level=%s "

            rs = cur.execute(sql, [item_level])
            if rs:
                items = cur.fetchall()
                itemsJson = Table2Json.item_list_to_json(items)
                cur.close()
                self.conn.close()
                return itemsJson
            else:
                return None
        elif item_status:
            sql = "select * from zzh_item WHERE item_status=%s "
            rs = cur.execute(sql, [item_status])
            if rs:
                items = cur.fetchall()
                itemsJson = Table2Json.item_list_to_json(items)
                cur.close()
                self.conn.close()
                return itemsJson
            else:
                return None

        else:
            return ItemDao().query_item()

    def conditions(self, **kwargs):
        """
        条件查询改进写法（替换上面的query_item_ifcondition(self, **kw)）
        :param kwargs:
        :return:
        """
        queryType = int(kwargs['queryType'])
        keyword = kwargs['keyword']
        classfyCode = kwargs['classfyCode']
        industryCode = kwargs['industryCode']
        departCode = kwargs['departCode']

        item_area = kwargs['item_area']
        item_level = kwargs['item_level']
        item_status = kwargs['item_status']
        cur = self.conn.cursor()

        if item_area == -1:
            conArea = 1
        else:
            arr = item_area.split('-')
            item_province = arr[0]
            item_town = arr[1]
            item_country = arr[2]

            if item_town == '不限区域':
                conArea = "item_province='%s'" % (item_province)
            elif item_country == '不限区域':
                conArea = "item_province='%s'  and item_town='%s' " % (item_province, item_town)
            else:
                conArea = "item_province='%s'  and item_town='%s' and item_country='%s'" % (
                    item_province, item_town, item_country)
        if item_level == -1:
            conLevel = 1
        else:
            conLevel = "item_level='%s'" % item_level
        if item_status == -1:
            conStatus = 1
        else:
            conStatus = "item_status='%s'" % item_status

        if queryType == 0:
            if keyword:
                con = "'%" + keyword + "%'"
                sql = "select * from zzh_item WHERE (industry_name LIKE %s or depart_name LIKE %s or handler_name LIKE %s) " \
                      "AND %s AND %s AND %s" % (con,con,con,conLevel,conStatus,conArea)
                rs = cur.execute(sql)
                if rs:
                    itemList = cur.fetchall()
                    cur.close()
                    self.conn.close()
                    itemJson = Table2Json.item_list_to_json(itemList)
                    return itemJson
                else:
                    return None
            else:
                sql1 = "select depart_name,depart_code from zzh_depart ORDER BY depart_sort limit 10"
                rs1 = cur.execute(sql1)
                departList = cur.fetchall()

                sql2 = "select industry_name,industry_code from zzh_industry ORDER BY industry_sort limit 10"
                rs2 = cur.execute(sql2)
                indusList = cur.fetchall()

                sql3 = "select handler_name from zzh_item ORDER BY handler_name limit 10"
                rs3 = cur.execute(sql3)
                handlerList = cur.fetchall()

                cur.close()
                self.conn.close()

                json2 = Table2Json.complexList(departList,indusList,handlerList)
                return json2

        elif queryType == 1:
            if keyword:
                con = "'%" + keyword + "%'"
                sql = "select * from zzh_item WHERE industry_name LIKE %s AND %s AND %s AND %s" % (
                     con, conLevel, conStatus, conArea)
                
                rs = cur.execute(sql)

                if rs:
                    itemList = cur.fetchall()
                    cur.close()
                    self.conn.close()
                    itemJson = Table2Json.item_list_to_json(itemList)
                    return itemJson
                else:
                    return None

            else:
                # sql = "select * from zzh_item WHERE %s AND %s AND %s AND" % (conArea, conStatus, conLevel)
                sql = "select industry_name,industry_code from zzh_industry ORDER BY industry_sort limit 10"
                rs = cur.execute(sql)
                indusList = cur.fetchall()
                cur.close()
                self.conn.close()
                indusJson = Table2Json.indus2json(indusList)
                return indusJson

        elif queryType == 2:
            if keyword:
                con = "'%" + keyword + "%'"
                sql = "select * from zzh_item WHERE depart_name LIKE %s AND %s AND %s AND %s " % (
                    con, conLevel, conStatus, conArea)
                rs = cur.execute(sql)
                if rs:
                    itemList = cur.fetchall()
                    cur.close()
                    self.conn.close()
                    itemJson = Table2Json.item_list_to_json(itemList)
                    return itemJson
                else:
                    return None

            else:
                # sql = "select * from zzh_item WHERE %s AND %s AND %s" % (conArea, conStatus, conLevel)
                sql = "select depart_name,depart_code from zzh_depart ORDER BY depart_sort limit 10"
                rs = cur.execute(sql)
                depList = cur.fetchall()
                cur.close()
                self.conn.close()
                depJson = Table2Json.depart2json(depList)
                return depJson



        elif queryType == 3:
            if keyword:
                con = "'%" + keyword + "%'"
                sql = "select * from zzh_item WHERE handler_name LIKE %s AND %s AND %s AND %s" % (
                    con, conArea, conStatus, conLevel)
                rs = cur.execute(sql)
                if rs:
                    itemList = cur.fetchall()
                    cur.close()
                    self.conn.close()
                    itemJson = Table2Json.item_list_to_json(itemList)
                    return itemJson
                else:
                    return None
            else:
                # sql = "select * from zzh_item WHERE %s AND %s AND %s AND" % (conArea, conStatus, conLevel)
                sql = "select handler_name from zzh_item ORDER BY handler_name limit 10"
                rs = cur.execute(sql)
                handList = cur.fetchall()
                cur.close()
                self.conn.close()
                handJson = Table2Json.hand2json(handList)
                return handJson
        else:
            if keyword:
                con = "'%" + keyword + "%'"
                sql = "select * from zzh_item WHERE (industry_name LIKE %s or depart_name LIKE %s or handler_name LIKE %s) " \
                      "AND classfy_pid=%s AND %s AND %s AND %s " % (con,con,con,
                          classfyCode, conArea, conLevel, conStatus)

            else:
                sql = "select * from zzh_item WHERE classfy_pid=%s AND %s AND %s AND %s" % (classfyCode,
                    conArea, conLevel, conStatus)

            rs = cur.execute(sql)
            if rs:
                items = cur.fetchall()
                itemsJson = Table2Json.item_list_to_json(items)
                cur.close()
                self.conn.close()
                return itemsJson
            else:
                return None

    def queryItemByItemId(self, itemId):
        cur = self.conn.cursor()
        sql = "select * from zzh_item WHERE item_id= %s "
        total_count = cur.execute(sql, [itemId])
        itemList = cur.fetchall()
        cur.close()
        self.conn.close()
        itemListJSON = Table2Json.item_list_to_json(itemList)
        return itemListJSON

    def query_hot_list(self, classfyCode, keyword):
        sql = "select * from zzh_item it,zzh_classfy cf WHERE cf.classfy_pid=%s AND it.classfy_pid=cf.classfy_code"

        cur = self.conn.cursor()
        rs = cur.execute(sql, [classfyCode])

        if rs:
            itemList = cur.fetchall()
            itemJson = Table2Json.classfy_and_item_to_json(itemList[:10])
            cur.close()
            self.conn.close()
            return itemJson
        else:
            cur.close()
            self.conn.close()
            return None

    def query_item_list(self, itemIds):
        cur = self.conn.cursor()
        ids = tuple(itemIds)
        print ids
        sql = "select * from zzh_item WHERE item_id IN %s"

        rs = cur.execute(sql, [ids])
        itemList = cur.fetchall()

        itemJson = Table2Json.item_list_to_json(itemList)
        return itemJson

    def queryCollectionInfo(self, userId, itemId):
        cur = self.conn.cursor()
        sql = "select collection_id,collection_status from zzh_collection WHERE user_id=%s AND item_id=%s AND collection_status=1"
        rs = cur.execute(sql, [userId, itemId])
        collectionIdAndCollectionStatus = cur.fetchall()
        collectionIdAndCollectionStatusJSON = Table2Json.colletionIdAndStatus_list_to_json(
            collectionIdAndCollectionStatus)
        if rs:
            return collectionIdAndCollectionStatusJSON
        else:
            return None

    def queryDeclarInfo(self, userId, itemId):
        cur = self.conn.cursor()
        sql = "select declar_id,declar_status from zzh_declar WHERE user_id=%s AND item_id=%s"
        rs = cur.execute(sql, [userId, itemId])
        declarIdAndDeclarStatus = cur.fetchall()
        declarIdAndDeclarStatusJSON = Table2Json.declarIdAndStatus_list_to_json(declarIdAndDeclarStatus)
        if rs:
            return declarIdAndDeclarStatusJSON
        else:
            return None

    def queryIndetnInfo(self, userId, itemId):
        cur = self.conn.cursor()
        sql = "select indent_id,indent_status from zzh_indent WHERE user_id=%s AND item_id=%s"
        rs = cur.execute(sql, [userId, itemId])
        indentIdAndIndentStatus = cur.fetchall()
        indentIdAndIndentStatusJSON = Table2Json.indentIdAndStatus_list_to_json(indentIdAndIndentStatus)
        if rs:
            return indentIdAndIndentStatusJSON
        else:
            return None
